Risc World 3/Risc World 3.iso/SOFTWARE/ISSUE3/POWERBASE/DOCS/Ch03-Print

Text File | 2001-01-30 | 49KB | 966 lines

=================================
Ch 3 − Printing from the Database
=================================

This chapter describes how to print out selected data from a database,
including the printing of individual records and of labels. Such a print-out
is commonly referred to as a report.

3.1     Output destination
--------------------------
Printed output may be displayed in a window, directed to a specified text
file or sent straight to the printer.  Choosing Options from the Print
submenu (keystroke equivalent Ctrl Print) displays the Print Options window
and you will see at the top of the window three radio buttons, Window, File
and Printer, which allow you to select the output destination. Each of these
options will now be described.

3.1.1   The Window destination

This is the default setting and, as its name suggests, it displays the
completed report in its own window. Clicking with MENU over this window
opens the Report menu which offers four choices:

Save as text leads to a Save box from which an icon may be dragged to a
filer window or to any application which can accept a Text file. The
supplied pathname uses an appropriate leaf-name for the file and points to a
directory called PrintJobs which is inside the database directory. Each
database has its own PrintJobs directory whose contents can be displayed by
choosing Show jobs done (Ctrl P) from the Print submenu. To save files with
the least bother simply click on Save or type Return. You can, of course,
delete the pathname (Ctrl U), type in your own filename and drag the file
icon to any open directory. You might also be able to produce hard copy by
“saving” the text to the Printers icon on the iconbar, although some recent
printers contain no fonts of their own and therefore cannot print text files
in this way. Look in PrintJobs regularly and get rid of files which you no
longer need.

Sort will sort the report on whatever column the mouse pointer was over when
you clicked MENU.

Shrink list will remove as much surplus “white space” as possible from
between the columns of a report. This item might be shaded: white-space
removal can be set to occur automatically before the report is displayed (in
fact this is the default setting). If this is the case no further space can
be removed and the menu choice is therefore made unavailable.

Scrap removes the report window from the screen and recovers the memory it
occupied. You might find this useful if you run short of memory after
creating a large report.

Calling up records from the report window
-----------------------------------------
The report window has one more useful feature. If you point at a particular
piece of displayed data and double-click with SELECT the record window will
come to the front displaying the relevant record. If the field corresponding
to the item you clicked is editable the caret will be placed in that field.
If you are using a report to look for errors in the data you can quickly
correct them by this method but the report won’t change to reflect your
corrections until you re-create it.  If you double-click with ADJUST instead
of SELECT the record window opens at the pointer and its size and scrolling
are adjusted to show only the required field.

3.1.2   The File destination

A Save box will appear as soon as you tell Powerbase to go ahead and
generate the report. The supplied pathname is the same as would be displayed
when saving from the report window. Simply click Save to save it in
PrintJobs under that name.  Alternatively, type a filename and drag the icon
to an open directory. The report will then be created and the file closed.
Nothing else appears on the screen in this case. The File destination is of
greatest use when reports are being produced from a Script file (see Ch 12).

3.1.3   The Printer destination

When the Printer radio button is selected the More button alongside becomes
available. (It is shaded when Window or File is selected.) Clicking More
opens the Printer Setup window which provides a wide range of options. You
may specify the number of copies to print, choose the font and point-size to
be used, print with the paper upright (portrait) or sideways (landscape) and
set the inter-line spacing and any or all of the four margins. All
measurements other than font size may be specified in mm (default), inches
or points.

3.2     Print formats
---------------------
Powerbase is capable of producing reports in four different formats. Only
two of these, Horizontal and Vertical, are available when you print to a

                                - 16 -

window or a file and these are selected via two radio buttons on the Print
Options window. When the output destination is Printer two additional
formats, Table and Label, are also available and are selected from the
Printer Setup window. These four formats will now be described.

3.2.1   Horizontal

This is so-called because each record appears on a single, horizontal line
with the fields aligned so that they form neat columns. Non-numeric fields
are left-justified. Numeric fields, and others whose content is treated as a
numeric value (see 3.5.2), are right-justified. A header line identifies the
columns by means of the tags or descriptors of the fields and this header
can be made to appear on every page of the report or on the first page only.
An optional descriptive title may also be incorporated. The Spacer is used
to separate columns (see 3.10). All these features (and others) are chosen
from the Print Options window.

Besides the ability to sort a report in a window (see 3.1.1) you may force
Powerbase to sort the report before displaying it. To do so, select the Sort
on option button in the Print Options window and enter in the associated
writable icon either the tag of the field on which you wish to sort or the
column number of the report. The feature is only of use in Horizontal and
Table formats, but may be used with the Printer destination as well as with
Window.

3.2.2   Vertical

Fields are printed underneath each other with the identifier (tag or
descriptor) at the left  hand side. Where the field selection includes an
external text file, i.e a Text or Text block field, the Vertical format is
the only one which may be used and will be selected automatically.  Although
each field normally occupies a line to itself you can override this by
holding down Shift as you click with ADJUST to select the field. You will
then not get a new line after the field: the next field to be selected will
appear (with its identifier) on the same line. We will call this function
field concatenation.  It may be applied to any number of fields: keep Shift
down while selecting all except the last one to appear on the line.  This
feature is very useful if your report contains a mixture of long fields
which need a line each and short ones which don’t and would otherwise result
in wasted space and paper.  Concatenated fields are separated by the Spacer
and the width of the report is governed by Text width  (see 3.10).

3.2.3   Table

This is only available when outputting to the printer. It resembles
Horizontal format but the lines and columns are separated by horizontal and
vertical rules, forming a grid. When this format is selected a number of
extra features are enabled allowing you to include extra (blank) columns and
lines, making this format especially useful when you want a list to which
information is to be added by hand (e.g. entering marks against a printed
list of students). The number and width of blank columns and the number of
extra lines may be specified. For a tidy result it is recommended that you
increase the line-spacing from the default 120% to about 150% when using
Table format.

3.2.4   Label

This is meant for printing on special label stationery. Since such
stationery is expensive you are advised to try out your settings on plain
paper first! Selecting this format enables the label setup choices which
include the label size and the number of labels in a row. It also allows
optional fixed starting and finishing lines to appear on each label. The
number of lines on the label is not needed: Powerbase works this out from
the label height and print size.

Printing will normally begin on the first label in the first row on the
sheet but, to enable you to use up a part sheet of labels, you may specify
which label to begin with, e.g. for three-in-a-row labels, entering 5 would
make printing start at the second label of the second row. After the first
sheet the starting-point reverts to the first label in the first row.

Each field normally appears on a separate line but fields may be
concatenated in the same way as for Vertical format, with the Spacer being
used to separate the fields (see 3.10).  This may be necessary if you are
using separate fields for surname and initials or surname and forename.

You may specify one field to be substituted for another if the latter is
blank. Both fields are specified by tag and the Substitute switch is set.
This is useful in a school or college situation where labels are being
addressed to parents.  Mature students, for whom the “parent” field in the
record is blank, can have their own names printed instead. Another switch
makes the primary key of the record appear in small print on each label as a

                                - 17 -

means of identification. This can be useful if the data printed on the label
doesn’t make it obvious which record it comes from.

3.3     What types of field can be printed?
-------------------------------------------
Powerbase can use many different types of field.  All are described in 4.2.5
in connection with setting up a new database and you should refer to that
section to clarify what follows here. You can print data from the following
types of field:

(1)     Any Editable field (i.e.one into which you can type directly),
        including Scrollable lists.

(2)     Computed and Stamp fields.

(3)     Text and Text block fields.

(4)     Check Boxes. What is printed for these differs from what you see in
        the check box.

Thus:   (a) Tick/Cross  boxes result in “Yes” or “No”
        (b) Tick/Blank  boxes result in “Yes” or “-”
        (c) Star/Blank  boxes result in an asterisk or “-”
        (d) Null/Tick/Cross boxes result in “-”, “Yes” or “No”
        (e) ?/Tick/Cross boxes result in “?”, “Yes” or “No”

Note that (d) and (e) are three-state check-boxes; (a), (b) and (c) are
two-state check-boxes. It is, of course, possible to print Draw and Sprite
fields from individual records by loading the external file into Draw or
Paint and printing from that application (See 2.6.2).

3.4     Specifying which fields to print
----------------------------------------
The field or fields selected for printing is called a print selection. Point
at each of the required fields and click with ADJUST. The fields will be
highlighted by reversing the foreground and background colours. Only those
fields which are printable (see 3.3) will respond to ADJUST in this way.  A
second click will de-select the field. Note that the order in which you
select the fields is important since that is the order in which they will
appear in the report. The menu button at the bottom-centre of the Match
window (see 3.5) will list the fields in the order in which they have been
selected. Ctrl F has the same effect. (If no fields are selected Ctrl F
gives a listing of all the fields.)

        A contiguous range of fields may be selected by placing the caret in
the first field then double-clicking with ADJUST in the last.  To select all
printable fields choose Select all (Ctrl A) from the Print submenu.  There
is also a Clear selection entry on this submenu (Ctrl Z).

3.4.1   Saving print selection files

Print selections may be saved for future use. Save selection from the Print
submenu leads to a Save box. Accepting the default pathname will save the
file with the name Selection in a directory called PrintRes. Just as every
database has its PrintJobs directory, so does it also have its own PrintRes
(i.e. “Print Resources”) directory whose contents can be displayed with Show
resources (Ctrl R) from the Print submenu. Selection files are of type &7f3
and are distinguished by their icon which bears a large S. Because they have
a specific file-type which Powerbase recognizes they can be loaded by
double-clicking on them.

3.4.2   Default selection

You may save as many print selections as you like and use any file-names,
but one name is special: a file saved as !Selection is treated as a default
selection. When you instruct Powerbase to create a report without having
first highlighted the fields to be included, the default selection will be
searched for in PrintRes and used. If there is no such file the primary key
field(s) will be printed. As soon as the report is complete the selection is
cleared. You won’t see the highlighting of the fields at all when a
selection is used automatically in this way.

        To save a default selection you need only select the option button
Default selection in the Save box and accept the supplied pathname by
clicking Save or typing Return.

3.5     Specifying which records to print
-----------------------------------------
Unless we want to print all the records in the database we need some means
of telling Powerbase what are the common features of the records we wish to
print. There are two ways of doing this. The more versatile way (and the one
which Powerbase uses by default) makes use of a search formula or query

                                - 18 -

describing the characteristics of the required records.  The remainder of
this section deals with the construction and use of search formulae.  For
the alternative method, query by example see Section 3.6.

If you choose Print from the main menu a small window with the title Find
matching records appears. We will call this the Match window.  The same
thing happens if you go to the Print submenu and choose Create report.
Simplest of all is to just type the Print key on the keyboard. The most
prominent feature of the Match window is a group of icons enclosed by a thin
red border. This object is called the Query panel and you may have already
seen it since it forms part of several windows. It appears when you select
the Filter switch on the keypad for example (see 2.3.3) and is also used
when making Global changes (2.5.5), performing a Move/delete operation on a
batch of records (2.5.6), doing a Mail merge (9.2.2), exporting a CSV file
(8.3) and creating a Subset (Ch 13).

        The writable icon in the Query panel, in whatever context the latter
appears, is meant to take a search formula. The simplest thing you can do,
of course, is to type nothing at all! If you then click on the Print  button
you will create a list of all the records in the current subfile of the
database. You could achieve the same result by typing ALL*, indeed if after
producing the above list with a null formula you click on the Old button
(Ctrl O), which retrieves the last-used search formula, you will find ALL
displayed.

3.5.1   The construction of search formulae

Most database queries will involve a selected group of records. A search
formula describes the criteria which records must fulfil in order to be
included in the report. Getting to grips with search formulae is, perhaps,
the biggest hurdle faced by the new Powerbase user and you are referred
first to the simple examples described in the Tutorial file. A search
formula consists of one or more search elements. A search element specifies
that a field value must fit a certain condition and takes the form:

        <TAG LIST><COMPARATOR><TARGET LIST>

The angle brackets are there for clarity and are not used in entering the
actual formula. There must be no spaces between the three parts. A tag list
(if it contains more than a single tag) has the form:

        tag1,tag2,tag3,....

where tag1 etc. are field tags (see 4.2.1 and 4.2.4) which identify the
fields to be matched. A target list (if it contains more than a single
target) has the form:-

        target1,target2,target3,....

If the Case switch on the Query panel is selected then all comparisons will
be case-specific, e.g. “Cat” will be regarded as different from “CAT” or
“cat”. If the Case switch is not selected all those three will be considered
identical. Fields are normally listed in ascending alphabetical order or,
for numeric fields, ascending numerical order. If the Reverse switch on the
Query panel is set they will appear in reverse order.

The heading of a report shows which fields were used in the search formula
and what targets were specified. If a target was placed in quotes (which is
the only way of searching for any string containing a comma, for example)
then it appears in quotes in the heading also.

It is impossible to describe the use of search formulae adequately without
quoting actual examples. As in the Tutorial file we will mainly make use of
the Elements sample database.  A simple example of a search formula
consisting of a single search element is:

        GP=T

where GP is the field tag, = is the comparator and T is the target. This
means “The field whose tag is GP must contain the value T”, i.e. all
transition elements (but no others) are to be included in the report. A
slightly more complex one is:

        GP=1,2,3

which would be interpreted as “The GP field must match one of 1,2 or 3”.
This may also be entered as:

        GP=1 OR GP=2 OR GP=3

which is possibly easier to understand but also somewhat longer.  A further
example is:

        OX1,OX2,OX3=3

meaning “One of the first three oxidation state fields must have the value
3”.
                                - 19 -

This could also be entered as:    OX1=3 OR OX2=3 OR OX3=3

Yet another way is:               OX1-OX3=3

i.e. you may specify a range of adjacent fields by giving the first and last
separated by a hyphen.  Where you don’t know which fields to test you can
replace the tag, tag list or tag range with @, which causes all the fields
in the record to be examined.

Note that in these examples only one of the fields in the tag list is
required to match one of the targets in the target list (although it doesn’t
matter if more than one field matches more than one target). Sometimes we
want an inclusive search so that all of the fields in the tag list match a
given target or, less frequently, a field contains all of the values in the
target list. It’s a matter of connecting the search elements with AND
instead of OR. You can do exactly that:

        OX1=3 AND OX2=3 AND OX3=3

You may also save typing by using the ampersand (&) instead of the word AND,
but the same result can be achieved even more briefly by simply doubling the
comparator, in other words using == instead of = so that the formula
becomes:

        OX1,OX2,OX3==3

The full list of available comparators is:

 =, <>, <, >, <=, >=, { and  }{       (N.B. ~ may be used instead of <>)

{ should be read as “contains” and  }{  as “does not contain”.  These are
used where the target value must (or must not) be part of the field but
isn’t expected to make up the whole field.  The use of { is the main source
of those rare instances where we want all the items in a target list to be
matched in a given field. e.g. Suppose we knew that someone’s house number
was 17 and that they lived on “<something> Avenue” but the actual name
couldn’t be remembered.  In a database of addresses a search formula such
as:

        ADDR{{17,Avenue

(note the doubled comparator) would find it, whereas:

        ADDR{17,Avenue

would find all those addresses where the house number was 17, regardless of
street name, and all those addresses with “Avenue” in them, whatever the
house number.

You may invert the logic of a search criterion by putting  NOT in front of
it. To print all non-transition elements you could use:

        NOT (GP=T)

Note the space after NOT, the need for brackets, and that the syntax isn’t
GP NOT=T. You could equally well use one of the following:

        GP<>T or GP~T

and may find either of these more understandable. To make multi-criterion
searches  either place tags and targets in comma-separated lists as
described above or string search elements together with the connectives AND
and OR. Use AND (or the ampersand, &) when a field must meet all of a set of
criteria. e.g.

        GP=T & Z>50 & NAME{IUM

for all transition metals with atomic numbers greater than 50 and names
containing IUM. Use OR when a field need meet only one of a set of criteria.
e.g.

        GP=L OR GP=A

would find all lanthanide and actinide elements as the formula means “either
L or A; I don’t care which”. AND and OR can produce ambiguous search
formulae e.g.

        GP=1 OR GP=2 AND Z<50

could mean either “elements in group 1 or 2 (don’t care which) with atomic
numbers less than 50” or  “group 1 elements (of any atomic number) or group
2 elements whose atomic numbers are less than 50”. You probably want the
former, but Powerbase will give you the latter. To get what you require use
brackets to make the logic clear. In other words write it as:

        (GP=1 OR GP=2) AND Z<50

You could also write this as:  GP=1,2 & Z<50  without the need for brackets
at all.

                                - 20 -

3.5.2   Numeric and other special fields in search formulae

For most types of field the comparison with the target is made by character
matching but for certain types the comparison uses the  numeric value of the
field. The fields concerned are:

Numeric, Calculated, Record number, Sequence number, Day of month, Month
number, Year

Only the first of these is an editable field type but all may be included in
search formulae.  Suppose we have a Numeric field whose tag is NUM.  If you
entered the formula:

        NUM=5

the record would be included in the report if NUM contained 5, 05, 5.0 etc.
because all of these have the same numeric value. If you had an Alphanumeric
field called NUM the same search formula would only match records where the
content was literally 5, i.e. the character “5”. This can easily catch you
out. Suppose, for example, you want to print records for which NUM<8. You
might be surprised to find records in which NUM contains values such as 55,
20, or 13 being printed, as well as those containing 4, 6, 2 etc!  If this
happens check what type of field NUM is.  Unrestricted and Alphanumeric
fields will give the above result; Numeric fields (and the others listed
above) will give the result you probably want.

You can force a comparison by numeric value for a field which consists of
(or, at least, begins with) numerals, even though the field is not defined
as of Numeric type,  by enclosing the field tag in square brackets, e.g.
[NUM]<8 would produce the desired result in the above example even if the
field is Alphanumeric or Unrestricted. This is useful where you want to make
a comparison but still allow the field to accept non-numeric characters. The
comparison-by-value can only work in such cases if the number part of the
field comes first. e.g. it will deal correctly with 55A, 20B, 13X but not
with A55, B20, X13.

3.5.3   Using “wild-cards” in search formulae

The use of characters “$” and “#” as “wild-cards” was described in 2.5.5 in
connection with search-and-replace operations. They may be also be used in
search formulae. “$” is used to represent a group of characters and “#” to
represent single characters which do not need to be matched.  e.g. If (still
using the Elements database) you type:

        NAME=$ON

you are, in effect, saying “find all the elements whose names end in ON; I
don’t care what precedes ON as long as nothing follows it”. Powerbase will
duly find CARBON, BORON, NEON etc.  If you were to use:

        NAME=$ON$

You would find PLUTONIUM, POLONIUM but not CARBON, BORON, NEON; something
must precede ON as well as follow it. (To print both sets of elements you
would use NAME{ON.)  Note also that:

        NAME=$TIN$

finds PROTOACTINIUM,  PLATINUM etc, but not TIN itself.

        NAME=S$IUM

finds all names which begin with S and end with IUM, e.g. SAMARIUM,
SCANDIUM, and SODIUM.  The effect of:

        NAME=S####IUM

is somewhat different. You are, again, asking for names which begin with S
and end with IUM but this time SAMARIUM and SCANDIUM would be found, but not
SODIUM since you have specified exactly 4 wild-carded letters between the S
and the I.  Finally, to find any 5-letter name, regardless of the actual
letters:

        NAME=#####

3.5.4   Comparing the contents of two fields

A field tag (instead of a literal string) may be specified as a target, thus
allowing two fields in a record to be compared to produce, for example,  a
list of all records in which the relevant fields have the same content.
This might interfere with a “normal” query where the required literal target
happens to be the same as the tag of another field. The problem can be
overcome by enclosing the literal string in quotes.

3.5.5   Saving search formulae for re-use

Choosing Save query from the Print submenu opens a Save box from which the
search formula may be saved. By default the file is saved in PrintRes under
the name Query. Selecting the Save as default switch on the Save box will

                                - 21 -

cause the file to be saved as the default query with the name !Query. If
such a file exists in PrintRes it will be automatically entered in the Query
panel whenever the Match window is opened. A default query file, in other
words, behaves in a similar way to a default selection file as described in
3.4.2. Query files are of type &7f4 and are recognizable by the large Q in
their icon. You may save as many Query files as you like and load them into
the query panel by double-clicking on them.

3.6     Query by example
------------------------
After that lengthy description of the search formula method of querying the
database we turn to the alternative: query by example.  For brevity when
comparing the two we will refer to them as SF and QBE respectively.  Select
the option switch at the top left of the Match window in order to use QBE.

3.6.1   What is QBE?

The user is presented with a blank record and invited to type into the
relevant fields the data which must be matched in order for the record to be
included in the report. What you are saying in effect is: “I want a list of
all records which look like this. I don’t care what’s in any of the fields I
haven’t filled in, but the ones I have filled in must correspond to what I
have typed.”  e.g. in the Elements database if you wanted to print a list of
all transition metals you would simply enter T in the Group field and then
proceed with the report.  The tag of the field isn't needed at all, whereas
using a search formula requires you to type GP=T. Enter the data to be
matched then  either click with SELECT on the Print button of the Match
window or else press the Print key.

If you simply enter the required target strings Powerbase assumes that you
want the relevant fields to match exactly, i.e the effect is the same as
using “=” in a search formula. There are, however, other  comparators
besides “=” which may be used in search formulae. (see 3.5.1 for explanation
and complete list). You may use any of these in a QBE query by placing them
at the start of the string, e.g. {Avenue in an Address field would match all
records where the field contained the word “Avenue”.  An address such as “15
Acacia Avenue” could be found by this method whereas just entering the word
“Avenue” wouldn’t work because it would require the field to read “Avenue”
and nothing more.

Wildcards may be used; e.g. you could print from the Elements database all
elements ending in IUM by entering $IUM in the NAME field or all those whose
symbol began with H by entering H# in the SYM field.

You may specify a target list (see 3.5.1) to make the search include all
records matching any item in the list. e.g. Leeds,Liverpool,Manchester in a
Town field (if it will fit) would cause records with any of these places to
be included. You can also specify a field list (equivalent to a tag list;
see 3.5.1) provided that the fields form a contiguous group. The target
string (which may be a target list, be wild-carded or be preceded by a
comparator) is entered in the first field of the group. Press Return and
enter " (double quote or “ditto” mark) in the next field and for the
remaining fields of the group. (Pressing Return rather than moving the caret
by means of the mouse ensures that you really are dealing with a contiguous
group of fields.)

3.6.2   QBE vs SF

So what are the advantages and disadvantages?  QBE is very intuitive and
avoids the need to bother with field tags or the minutiae of search formula
syntax. On the other hand SF is more comprehensive and flexible: there are
some things you simply cannot do with QBE.  Some of the limitations have
already been mentioned but here is a complete list.

•       Except where exact matches (“field=target string”) are required it
        might not be possible to fit the target string or list into the
        relevant field.  One consequence is that you can't target a Date
        field for all dates prior to, say, 01-06-90 since dates fit their
        fields exactly leaving no room for the necessary < comparator.  Such
        a search is perfectly possible with SF.

•       Searches involving multiple fields and the same target, i.e. those
        where FieldX or FieldY or FieldZ must match are only possible where
        the fields form a contiguous sequence. With SF it is possible to
        perform such searches on fields dotted about the record.

•       There is no equivalent in QBE to the @= (any field in the record
        matches) or @{ (any field contains) searches which are possible with
        SF.

•       You can't search for text in an external Text or Text Block field.
        You can with SF.

                                - 22 -

•       You can't include Check-box fields in QBE searches.

•       Since QBE doesn't use tags you can’t force a
        comparison-by-numeric-value on a non-numeric field (See 3.5.2).

•       Calculated fields and those belonging to the Stamp class cannot be
        used by QBE because you cannot type into them!

If your requirements involve only fairly simple searches then QBE might be
just what you’ve been looking for, but if complex multiple searches are
often needed then SF is the one to go for. It is, of course, a simple matter
to switch between the two.

As supplied Powerbase uses SF as the default query method and the Query by
example switch will be deselected when the Match window is displayed. If you
want to make QBE the default you can edit the relevant line of the Config
file in !Powerbase.Resources to read Query QBE instead of Query SF. Don’t
forget the space. Selecting the Print function will then produce the blank
record without displaying the Match window at all.  There is, of course, no
Print button to click with the mouse after you have entered the target
strings so you tell Powerbase proceed by typing the Print key after entering
the data to be matched.

3.7     Other features of the Query panel and Match window
----------------------------------------------------------
At the far left of the Match window is a group of four radio buttons
labelled Print, Count, Mark and Clear. Only one of these may be selected at
a time and the default action button at the bottom right of the window
reflects whichever one you select. When the Match window is opened it is
always Print which is selected, this being the most often used feature. If
you merely want to know how many records match a specified set of criteria,
without printing them, select Count. The number of matching records appears
to the left of the Cancel button (which merely closes the window).  Mark and
Clear are explained in section 3.8.2 below.

The icon to the left of Cancel indicates the selected output destination
(see 3.1) by displaying a representation of a window, a text-file icon, or a
printer. In the latter case the icon will be shaded if no printer driver is
loaded. Clicking with SELECT on the icon opens the Print Options window.

Old, as stated earlier, retrieves the last search formula used. The action
of the Case and Reverse switches has also been described (see 3.5.1).

Help opens the Help window which offers another way of building search
formulae which might appeal to beginners. Select the target field by cycling
through the tags with the bump icons or by choosing from the pop-up menu.
Choose the comparator by selecting a radio button. Type the target value
into the writable icon. Place the caret in the Query panel writable icon and
click Add to formula.  The search element will appear at the caret.  You may
click on AND or OR and enter other search elements in the same way. If you
wish to use the NOT button you must do so before clicking Add to formula.
Powerbase inserts the brackets round the search element for you.

Holding down Ctrl and clicking on a field with SELECT while the caret is in
the Query panel causes the tag of the field to be entered in the search
formula at the caret. This, together with the above method of constructing
search formulae, largely overcomes the problem (especially when using
someone else’s database) of not remembering what the field tags are.

3.7.1   Printing records from more than one subfile

Reports are usually created from records in the currently-selected subfile
which is displayed in the title bar of the record window. Just under the
Query panel is the legend Incl.subfiles: and a row of numerals, 0-5. When a
database is opened 0 will be highlighted, indicating that reports will only
include records from subfile 0. If you change subfile by clicking on the
appropriate keypad buttons you will see this highlighting move from one
number to another, showing the selected subfile. You can, however, click on
these numbers so that any or all of them are selected. Subfiles are
deselected with a second click.

When you create a report from more than one subfile the records are not
merged into one alphabetically (or numerically) ordered list; the ordering
starts afresh for each selected subfile. This isn’t really a problem because
you can always sort the completed report  on any field to produce a single,
ordered list (see 3.1.1).

3.7.2   Including record number, key and subfile number

A group of three option switches in a frame to the left of the query panel
allow you to include record numbers, the current key, and the subfile number
in a report. The latter is especially useful if you have created a report
from several subfiles and then sorted it as described above. If you need to

                                - 23 -

keep track of which subfile a record comes create the report with File
selected. These three special items are not saved with a print selection
file and always appear on a report before any of the selected fields.

3.8     Marking records for inclusion or exclusion
--------------------------------------------------
There are times when you want to print a number of records which have no
obvious connection with one another: they may have a common feature which is
obvious to you but none within the records themselves.  Such a situation
commonly occurs when you want to print a few mailing labels.  No common
feature means no basis for constructing a search formula. So how do you tell
Powerbase which records you want to print?

3.8.1   Single records

A small panel attached to the bottom of the record window* contains a
check-box, Mark for printing etc., which you can tick to indicate that the
displayed record is to be printed.  Using the Search button or the browse
controls you can call up each record you want and tick the box. You then
simply select the required fields and print in the usual way. If no search
formula has been entered then only the marked records will be printed.
(Printing without a search formula when no records are marked gives the
whole subfile as described in 3.5)  If you do enter a search formula you
will get the records which match the formula plus the marked records,
whether the latter match the formula or not.

A pop-up menu allows you to invert the effect of this feature so that
printing without a search formula gives all records in the subfile except
the marked ones and printing with a search formula gives all the matching
records except for those marked. When the menu option is set like this (to
exclude rather than include) the check box shows a red cross instead of a
green tick.

Next to the check-box is the Clear marks button which does exactly what it
says. It is shaded when no record is marked. A further indication of whether
records are marked is provided by the small rectangle at the far right of
the Query panel which is coloured green or red, as appropriate, when any
record is marked. This applies to the whole database, by the way, not just
to the current subfile.

Powerbase takes heed of marked records in any operation which involves the
query panel, i.e. batch move/delete, global change, filter, export subset,
export CSV file, as well as print.

3.8.2   Groups of records

It is sometimes useful to be able to mark (or clear marks from) a group of
records which fit a search formula. This is made possible by the Mark and
Clear radio buttons on the Match window.  By repeatedly selecting Mark and
executing different query operations you can build up a set of marked
records by stages then, if desired, selectively clear the marks from certain
ones. Finally, you can print your carefully-tailored selection of marked
records without entering a search formula at all.

3.9     Printing single records
-------------------------------
There are two methods of printing the displayed record only:

(a) Hold down Shift whilst clicking with SELECT on the Print button of the
Match window or type Shift Print on the keyboard. The highlighted fields of
the displayed record are printed using the currently-selected print format
as determined by the setting in the Print options window.  If no fields are
selected the action is as described in 3.4.2; Powerbase will use the default
selection if it exists or, failing that, print the primary key fields only.

(b) Follow Export selected from the main menu to a Save box from which may
be saved (or dragged to the printer or into a wordprocessor document) a text
file containing the highlighted fields of the displayed record. If there are
no fields selected the menu entry is shaded.

3.10    The Print options window
--------------------------------
To display this window you can choose Options from the Print submenu, type
Ctrl Print, or click SELECT on the icon to the left of Cancel on the Match
window. Features such as Destination (see 3.1), Format (see 3.2)  and the
Sort on facility (see 3.2.1) have already been dealt with extensively. The
rest are covered here. Default settings appear in brackets after the name of
the feature.

Scrollable lists (as single row) These may be printed in two different ways.

                                - 24 -

The default is for all the cells in the list to be made into a single row
with semicolons separating the data from individual rows of the scroller.
This can result in very long lines indeed. An option switch (Shrink row - ON
by default) causes as much white space as possible to be removed, but lines
could still be too long for the printer. The alternative format puts the
data from each row of the scroller on a separate line so that the data
aligns in columns. This occupies less room horizontally but much more
vertically.  Experiment!

Headings (tags) appear at the head of reports in all formats except Label
unless None is selected.

Expand codes (OFF) causes extra data from a validation table to be
substituted for (or added to) the coded data in fields linked to such tables
(see 5.2).

Expand headers (ON) will show the expanded versions (see 5.2) of the target
values for fields linked to validation tables in the list header.  Turning
the option OFF causes the target values to be shown exactly as typed in the
search formula.

Upper case (OFF) causes all textual output to appear in capital letters.

Print header (ON) causes the printing of header lines at the beginning of
each page.  The header includes the following information:

•       The name of the database plus a title, derived from the search
        formula, making it clear on what basis the records have been chosen.

•       The index used for the ordering, plus the date stamp (if appropriate
        switch selected).

•       An optional description entered in the Title writable icon.

•       The column headings as described above.

The following switch (on p. 1 only: default OFF) limits the header to the
first page of a report.

Print footer (ON). Reports in Horizontal and Table format normally end with
a footer which specifies the number of records printed.  If the output
includes Numeric fields and column calculations have been selected (see 6.4)
then the results of these too will be part of the footer.

Date stamp (ON) makes the date and time when the report was created appear
as part of the header.

Shrink list (ON). In Horizontal and Table format the width of columns is
determined by the maximum defined length of the fields included in the print
selection. These lengths are often greater than the length of data actually
present in the fields, resulting in a lot of “white space” between columns.
With this option ON the surplus space will be automatically removed. Even if
it is OFF you can still remove white space via the Report menu (see 3.1.1).
Output to Printer always removes white space whether this switch is ON or
OFF

Page numbers (OFF) allows page numbers  to appear at the bottom of each page
of a report. This feature works quite independently of the Print footer
switch.

Page length (0) determines the total length of page, including header,
footer and top margin, for destinations other than Printer (for which the
page length is determined by the printer driver).  The default value of 0
means no division into pages at all, but you might want to alter this if you
drag text-files to the printer. An A4 page is 70 lines long, but you won’t
be able to print on them all and page-feeds might occur in the wrong place.
Look at Edit paper sizes on the iconbar menu of Printers. Subtract the
displayed top and bottom text margins from 70 and enter the value in place
of 64, if necessary. In Vertical format Powerbase will try to avoid
splitting a record between pages, but this can happen if the report includes
Text or Text Block fields of greatly varying length. (It will also happen if
the number of fields to be printed exceeds the length of the page!)

Text width (A) specifies the line length used when printing in Vertical
format. A means “Auto” and lets the program calculate the value. You may
enter a value of your own (e.g. 70) to override this.

Spacer (1) specifies how fields printed on the same line will be separated.
Fields are first padded with spaces to the maximum width of the relevant
data field (but see Shrink list above) and the spacer string is then printed
before starting the next field. Three interpretations of the contents of
this setting are possible:

•       A number by itself means use the specified number of spaces.

•       A number followed by a non-numeric character means use a string of
        the specified number of that character, e.g. “3-” means 3 hyphens.

•       A non-numeric string is used “as is”, e.g.“|” or “ | ”. If the
        former is used when outputting to Printer continuous vertical rules
        will be placed between columns in Horizontal format.

                                -  25 -

3.10.1 Saving print options files

All the settings in the Print Options and Printer Setup windows may be saved
as a Print Options file. Clicking Save choices with the in database radio
button selected brings up the familiar Save box. By default the file is
saved in PrintRes under the name PrintOpts. As with Selection (see 3.4.1)
and Query files (see 3.5.5) you can save a default options file called
!PrintOpts by selecting the Default options button on the Save box and
Powerbase will load this whenever the database is opened. Options files have
a large P on their icon and a filetype of &7f5. You may save as many as you
wish and load them with a double-click. If the in Powerbase radio button is
selected the options are saved as the Powerbase default and no Save box is
displayed. Load default reloads this file, overwriting any changed settings.

3.11    Field analysis reports
------------------------------
The Field submenu has an Analyse option which allows you to print a
breakdown of the field contents under certain special circumstances:

(a)  If the field is indexed the menu entry will read Analyse index. When
chosen it will produce a list of all the values in the index with the number
of times each one occurs. e.g. A database of college students might have a
field for the school of origin. If there are 20 different schools and if the
field is indexed then a list of those schools will be generated showing how
many students came from each school.

(b) If the field contains an 8 or 10 character date (e.g. 19-10-42 or
19-10-1942) the menu entry reads Analyse months and a breakdown by month
will be printed. There will be a line for each month showing the number of
records for that month. For example, this could be used in an orders
database to find out quickly how many orders were received or dispatched
each month. This feature works on editable Date fields and also on Date
stamp8 and Date stamp10 fields in the Stamp class.

It is, of course, possible for a field containing a date to be indexed.
Action (a), above, takes precedence in such a case. You can, however, force
action (b) instead by first selecting the field with ADJUST, then choosing
from the menu. For cases not described above the menu entry simply says
Analyse and is shaded. Printing is always to a window (from which the report
may, of course, be saved); the Destination buttons in the Print options
window have no effect.

3.12    Subsidiary indices and printing speed
---------------------------------------------
Try the following experiment using the sample database Elements :

(1)     Create a subsidiary index, if one doesn’t already exist, on the
        Group field (see 7.1 for the way to do this). Don’t make the index
        case-specific

(2)     Enter the formula GP=T and click Print with ADJUST so that the Match
        window remains open. Note the time taken for creating the report.

(3)     Deselect the Case button and repeat the process.  The time will be
        shorter.  You might also notice the brief appearance of a numeral
        (probably 1) in the small rectangle to the right of the search
        formula in the Query panel.

The speed increase is most noticeable with a slow machine such as an A3000.
On a StrongArm RiscPC and with such a small database both times will be so
short that the user’s reaction might be “So what?” but when dealing with
databases of thousands of records the improvement can be quite dramatic.

What happens is that Powerbase detects the fact that there is an index based
on the Group (GP) field, goes straight to the first occurrence of “T” in
that index then prints records for as long as the key remains “T”.  The
number briefly displayed in the Query panel is the number of the index being
used and its appearance shows the user that a subsidiary index is being
used.  If you look at the header of the report you will see that it says
“Ordered by GP index” whereas the first time it said “Ordered by
PrimaryKey”.  The speed-up only works when the all following conditions are
met:

•       The search formula must include a simple comparison for equality,
        i.e. of the type TAG=target, without alternatives: no OR, no tag
        list,  no target list.

•       The whole of the field represented by TAG must be indexed.

•       The Case switch on the Query panel must be set to agree with the
        index, i.e. it must be selected if the index is case-specific and
        deselected if not.

                                - 26 -